WITH q1(x,y) AS (SELECT 1,2)SELECT * FROM q1, q1 AS q2;
SELECT count(*) FROM (  WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))    SELECT * FROM q1  UNION    SELECT * FROM q1) ss;
WITH RECURSIVE t(n) AS (    VALUES (1)UNION ALL    SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;
WITH RECURSIVE t(n) AS (    SELECT (VALUES(1))UNION ALL    SELECT n+1 FROM t WHERE n < 5)SELECT * FROM t;
CREATE RECURSIVE VIEW nums (n) AS    VALUES (1)UNION ALL    SELECT n+1 FROM nums WHERE n < 5;
SELECT * FROM nums;
CREATE OR REPLACE RECURSIVE VIEW nums (n) AS    VALUES (1)UNION ALL    SELECT n+1 FROM nums WHERE n < 6;
SELECT * FROM nums;
WITH RECURSIVE t(n) AS (    SELECT 1UNION    SELECT 10-n FROM t)SELECT * FROM t;
WITH RECURSIVE t(n) AS (    VALUES (1)UNION ALL    SELECT n+1 FROM t)SELECT * FROM t LIMIT 10;
WITH RECURSIVE t(n) AS (    SELECT 1UNION    SELECT n+1 FROM t)SELECT * FROM t LIMIT 10;
WITH q AS (SELECT 'foo' AS x)SELECT x, x IS OF (text) AS is_text FROM q;
WITH RECURSIVE t(n) AS (    SELECT 'foo'UNION ALL    SELECT n || ' bar' FROM t WHERE length(n) < 20)SELECT n, n IS OF (text) AS is_text FROM t;
WITH RECURSIVE t(n) AS (    SELECT '7'UNION ALL    SELECT n+1 FROM t WHERE n < 10)SELECT n, n IS OF (int) AS is_int FROM t;
WITH RECURSIVE w1(c1) AS (WITH w2(c2) AS  (WITH w3(c3) AS   (WITH w4(c4) AS    (WITH w5(c5) AS     (WITH RECURSIVE w6(c6) AS      (WITH w6(c6) AS       (WITH w8(c8) AS        (SELECT 1)        SELECT * FROM w8)       SELECT * FROM w6)      SELECT * FROM w6)     SELECT * FROM w5)    SELECT * FROM w4)   SELECT * FROM w3)  SELECT * FROM w2)SELECT * FROM w1;
WITH RECURSIVE outermost(x) AS ( SELECT 1 UNION (WITH innermost1 AS (  SELECT 2  UNION (WITH innermost2 AS (   SELECT 3   UNION (WITH innermost3 AS (    SELECT 4    UNION (WITH innermost4 AS (     SELECT 5     UNION (WITH innermost5 AS (      SELECT 6      UNION (WITH innermost6 AS       (SELECT 7)       SELECT * FROM innermost6))      SELECT * FROM innermost5))     SELECT * FROM innermost4))    SELECT * FROM innermost3))   SELECT * FROM innermost2))  SELECT * FROM outermost  UNION SELECT * FROM innermost1) ) SELECT * FROM outermost ORDER BY 1;
CREATE TEMP TABLE department (	id INTEGER PRIMARY KEY,  	parent_department INTEGER REFERENCES department, 	name TEXT );
INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 5, 'G');
WITH RECURSIVE subdepartment AS(		SELECT name as root_name, * FROM department WHERE name = 'A'	UNION ALL		SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd		WHERE d.parent_department = sd.id)SELECT * FROM subdepartment ORDER BY name;
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS(		SELECT 1, * FROM department WHERE name = 'A'	UNION ALL		SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd		WHERE d.parent_department = sd.id)SELECT * FROM subdepartment ORDER BY name;
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS(		SELECT 1, * FROM department WHERE name = 'A'	UNION ALL		SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd		WHERE d.parent_department = sd.id)SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
WITH RECURSIVE subdepartment AS(		SELECT * FROM department WHERE name = 'A')SELECT * FROM subdepartment ORDER BY name;
SELECT count(*) FROM (    WITH RECURSIVE t(n) AS (        SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500    )    SELECT * FROM t) AS t WHERE n < (        SELECT count(*) FROM (            WITH RECURSIVE t(n) AS (                   SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100                )            SELECT * FROM t WHERE n < 50000         ) AS t WHERE n < 100);
WITH q1(x,y) AS (    SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred  )SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
CREATE TEMPORARY VIEW vsubdepartment AS	WITH RECURSIVE subdepartment AS	(		 		SELECT * FROM department WHERE name = 'A'		UNION ALL				SELECT d.* FROM department AS d, subdepartment AS sd			WHERE d.parent_department = sd.id	)	SELECT * FROM subdepartment;
SELECT * FROM vsubdepartment ORDER BY name;
SELECT pg_get_viewdef('vsubdepartment'::regclass);
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
CREATE VIEW sums_1_100 ASWITH RECURSIVE t(n) AS (    VALUES (1)UNION ALL    SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;
\d+ sums_1_100with recursive q as (      select * from department    union all      (with x as (select * from q)       select * from x)    )select * from q limit 24;
with recursive q as (      select * from department    union all      (with recursive x as (           select * from department         union all           (select * from q union all select * from x)        )       select * from x)    )select * from q limit 32;
WITH RECURSIVE t(i,j) AS (	VALUES (1,2)	UNION ALL	SELECT t2.i, t.j+1 FROM		(SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2		JOIN t ON (t2.i = t.i+1))	SELECT * FROM t;
CREATE TEMPORARY TABLE tree(    id INTEGER PRIMARY KEY,    parent_id INTEGER REFERENCES tree(id));
INSERT INTO treeVALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),       (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
WITH RECURSIVE t(id, path) AS (    VALUES(1,ARRAY[]::integer[])UNION ALL    SELECT tree.id, t.path || tree.id    FROM tree JOIN t ON (tree.parent_id = t.id))SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON	(t1.path[1] = t2.path[1] AND	array_upper(t1.path,1) = 1 AND	array_upper(t2.path,1) > 1)	ORDER BY t1.id, t2.id;
WITH RECURSIVE t(id, path) AS (    VALUES(1,ARRAY[]::integer[])UNION ALL    SELECT tree.id, t.path || tree.id    FROM tree JOIN t ON (tree.parent_id = t.id))SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON	(t1.path[1] = t2.path[1] AND	array_upper(t1.path,1) = 1 AND	array_upper(t2.path,1) > 1)	GROUP BY t1.id	ORDER BY t1.id;
WITH RECURSIVE t(id, path) AS (    VALUES(1,ARRAY[]::integer[])UNION ALL    SELECT tree.id, t.path || tree.id    FROM tree JOIN t ON (tree.parent_id = t.id))SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON(t1.id=t2.id);
create temp table graph( f int, t int, label text );
insert into graph values	(1, 2, 'arc 1 -> 2'),	(1, 3, 'arc 1 -> 3'),	(2, 3, 'arc 2 -> 3'),	(1, 4, 'arc 1 -> 4'),	(4, 5, 'arc 4 -> 5'),	(5, 1, 'arc 5 -> 1');
with recursive search_graph(f, t, label, path, cycle) as (	select *, array[row(g.f, g.t)], false from graph g	union all	select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)	from graph g, search_graph sg	where g.f = sg.t and not cycle)select * from search_graph;
with recursive search_graph(f, t, label, path, cycle) as (	select *, array[row(g.f, g.t)], false from graph g	union all	select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)	from graph g, search_graph sg	where g.f = sg.t and not cycle)select * from search_graph order by path;
WITH RECURSIVE  y (id) AS (VALUES (1)),  x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)SELECT * FROM x;
WITH RECURSIVE    x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),    y(id) AS (values (1)) SELECT * FROM x;
WITH RECURSIVE   x(id) AS     (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),   y(id) AS     (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10) SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
WITH RECURSIVE   x(id) AS     (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),   y(id) AS     (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10) SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
WITH RECURSIVE   x(id) AS     (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),   y(id) AS     (SELECT * FROM x UNION ALL SELECT * FROM x),   z(id) AS     (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10) SELECT * FROM z;
WITH RECURSIVE   x(id) AS     (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),   y(id) AS     (SELECT * FROM x UNION ALL SELECT * FROM x),   z(id) AS     (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10) SELECT * FROM z;
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
WITH t AS (	SELECT a FROM y)INSERT INTO ySELECT a+20 FROM t RETURNING *;
SELECT * FROM y;
WITH t AS (	SELECT a FROM y)UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
SELECT * FROM y;
WITH RECURSIVE t(a) AS (	SELECT 11	UNION ALL	SELECT a+1 FROM t WHERE a < 50)DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
SELECT * FROM y;
DROP TABLE y;
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)	SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)	SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)	SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)	SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT n FROM x)	SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)	SELECT * FROM x;
CREATE TEMPORARY TABLE y (a INTEGER);
INSERT INTO y SELECT generate_series(1, 10);
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1	UNION ALL	SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1	UNION ALL	SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1	UNION ALL	SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x                          WHERE n IN (SELECT * FROM x))  SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)  SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)  SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)  SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)  SELECT * FROM x;
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)  SELECT * FROM x;
WITH RECURSIVE x(id) AS (values (1)    UNION ALL    SELECT (SELECT * FROM x) FROM x WHERE id < 5) SELECT * FROM x;
WITH RECURSIVE  x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),  y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)SELECT * FROM x;
WITH RECURSIVE foo(i) AS    (values (1)    UNION ALL       (SELECT i+1 FROM foo WHERE i < 10          UNION ALL       SELECT i+1 FROM foo WHERE i < 5)) SELECT * FROM foo;
WITH RECURSIVE foo(i) AS    (values (1)    UNION ALL	   SELECT * FROM       (SELECT i+1 FROM foo WHERE i < 10          UNION ALL       SELECT i+1 FROM foo WHERE i < 5) AS t) SELECT * FROM foo;
WITH RECURSIVE foo(i) AS    (values (1)    UNION ALL       (SELECT i+1 FROM foo WHERE i < 10          EXCEPT       SELECT i+1 FROM foo WHERE i < 5)) SELECT * FROM foo;
WITH RECURSIVE foo(i) AS    (values (1)    UNION ALL       (SELECT i+1 FROM foo WHERE i < 10          INTERSECT       SELECT i+1 FROM foo WHERE i < 5)) SELECT * FROM foo;
WITH RECURSIVE foo(i) AS   (SELECT i FROM (VALUES(1),(2)) t(i)   UNION ALL   SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)SELECT * FROM foo;
WITH RECURSIVE foo(i) AS   (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)   UNION ALL   SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)SELECT * FROM foo;
CREATE TEMPORARY TABLE x (n integer);
CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD    WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
with cte(foo) as ( values(42) ) values((select foo from cte));
with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
select ( with cte(foo) as ( values(f1) )         select (select foo from cte) )from int4_tbl;
select ( with cte(foo) as ( values(f1) )          values((select foo from cte)) )from int4_tbl;
WITH RECURSIVE t(j) AS (    WITH RECURSIVE s(i) AS (        VALUES (1)        UNION ALL        SELECT i+1 FROM s WHERE i < 10    )    SELECT i FROM s    UNION ALL    SELECT j+1 FROM t WHERE j < 10)SELECT * FROM t;
WITH outermost(x) AS (  SELECT 1  UNION (WITH innermost as (SELECT 2)         SELECT * FROM innermost         UNION SELECT 3))SELECT * FROM outermost ORDER BY 1;
WITH outermost(x) AS (  SELECT 1  UNION (WITH innermost as (SELECT 2)         SELECT * FROM outermost           UNION SELECT * FROM innermost))SELECT * FROM outermost ORDER BY 1;
WITH RECURSIVE outermost(x) AS (  SELECT 1  UNION (WITH innermost as (SELECT 2)         SELECT * FROM outermost         UNION SELECT * FROM innermost))SELECT * FROM outermost ORDER BY 1;
WITH RECURSIVE outermost(x) AS (  WITH innermost as (SELECT 2 FROM outermost)     SELECT * FROM innermost    UNION SELECT * from outermost)SELECT * FROM outermost ORDER BY 1;
withA as ( select q2 as id, (select q1) as x from int8_tbl ),B as ( select id, row_number() over (partition by id) as r from A ),C as ( select A.id, array(select B.id from B where B.id = A.id) from A )select * from C;
WITH RECURSIVE  tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),  iter (id_key, row_type, link) AS (      SELECT 0, 'base', 17    UNION ALL (      WITH remaining(id_key, row_type, link, min) AS (        SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()        FROM tab INNER JOIN iter USING (link)        WHERE tab.id_key > iter.id_key      ),      first_remaining AS (        SELECT id_key, row_type, link        FROM remaining        WHERE id_key=min      ),      effect AS (        SELECT tab.id_key, 'new'::text, tab.link        FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key        WHERE e.row_type = 'false'      )      SELECT * FROM first_remaining      UNION ALL SELECT * FROM effect    )  )SELECT * FROM iter;
WITH RECURSIVE  tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),  iter (id_key, row_type, link) AS (      SELECT 0, 'base', 17    UNION (      WITH remaining(id_key, row_type, link, min) AS (        SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()        FROM tab INNER JOIN iter USING (link)        WHERE tab.id_key > iter.id_key      ),      first_remaining AS (        SELECT id_key, row_type, link        FROM remaining        WHERE id_key=min      ),      effect AS (        SELECT tab.id_key, 'new'::text, tab.link        FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key        WHERE e.row_type = 'false'      )      SELECT * FROM first_remaining      UNION ALL SELECT * FROM effect    )  )SELECT * FROM iter;
WITH t AS (    INSERT INTO y    VALUES        (11),        (12),        (13),        (14),        (15),        (16),        (17),        (18),        (19),        (20)    RETURNING *)SELECT * FROM t;
SELECT * FROM y;
WITH t AS (    UPDATE y    SET a=a+1    RETURNING *)SELECT * FROM t;
SELECT * FROM y;
WITH t AS (    DELETE FROM y    WHERE a <= 10    RETURNING *)SELECT * FROM t;
SELECT * FROM y;
WITH RECURSIVE t AS (	INSERT INTO y		SELECT a+5 FROM t2 WHERE a > 5	RETURNING *), t2 AS (	UPDATE y SET a=a-11 RETURNING *)SELECT * FROM tUNION ALLSELECT * FROM t2;
SELECT * FROM y;
CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD  INSERT INTO y VALUES(42) RETURNING *;
WITH t AS (	DELETE FROM y RETURNING *)SELECT * FROM t;
SELECT * FROM y;
DROP RULE y_rule ON y;
CREATE TEMP TABLE bug6051 AS  select i from generate_series(1,3) as t(i);
SELECT * FROM bug6051;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
CREATE TEMP TABLE bug6051_2 (i int);
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD INSERT INTO bug6051_2 SELECT NEW.i;
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )INSERT INTO bug6051 SELECT * FROM t1;
SELECT * FROM bug6051;
SELECT * FROM bug6051_2;
WITH RECURSIVE t(a) AS (	SELECT 0		UNION ALL	SELECT a+1 FROM t WHERE a+1 < 5), t2 as (	INSERT INTO y		SELECT * FROM t RETURNING *)SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
SELECT * FROM y;
WITH t AS (    DELETE FROM y    WHERE a <= 10    RETURNING *)INSERT INTO y SELECT -a FROM t RETURNING *;
SELECT * FROM y;
WITH t AS (    UPDATE y SET a = a * 100 RETURNING *)SELECT * FROM t LIMIT 10;
SELECT * FROM y;
CREATE TABLE withz AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
ALTER TABLE withz ADD UNIQUE (k);
WITH t AS (    INSERT INTO withz SELECT i, 'insert'    FROM generate_series(0, 16) i    ON CONFLICT (k) DO UPDATE SET v = withz.v || ', now update'    RETURNING *)SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
WITH aa AS (    INSERT INTO withz VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v    WHERE withz.k != EXCLUDED.k    RETURNING *)SELECT * FROM aa;
SELECT * FROM withz ORDER BY k;
WITH aa AS (SELECT 1 a, 2 b)INSERT INTO withz VALUES(1, 'insert')ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)INSERT INTO withz VALUES(1, 'insert')ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE withz.k = (SELECT a FROM aa);
WITH aa AS (SELECT 1 a, 2 b)INSERT INTO withz VALUES(1, 'insert')ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b)INSERT INTO withz VALUES(1, 'insert')ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1);
WITH aa AS (SELECT 1 a, 2 b)INSERT INTO withz VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 ))ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
WITH simpletup AS (  SELECT 2 k, 'Green' v),upsert_cte AS (  INSERT INTO withz VALUES(2, 'Blue') ON CONFLICT (k) DO    UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = withz.k)    RETURNING k, v)INSERT INTO withz VALUES(2, 'Red') ON CONFLICT (k) DOUPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = withz.k)RETURNING k, v;
DROP TABLE withz;
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 3);
CREATE TEMPORARY TABLE yy (a INTEGER);
WITH RECURSIVE t1 AS (  INSERT INTO y SELECT * FROM y RETURNING *), t2 AS (  INSERT INTO yy SELECT * FROM t1 RETURNING *)SELECT 1;
SELECT * FROM y;
SELECT * FROM yy;
WITH RECURSIVE t1 AS (  INSERT INTO yy SELECT * FROM t2 RETURNING *), t2 AS (  INSERT INTO y SELECT * FROM y RETURNING *)SELECT 1;
SELECT * FROM y;
SELECT * FROM yy;
TRUNCATE TABLE y;
INSERT INTO y SELECT generate_series(1, 10);
CREATE FUNCTION y_trigger() RETURNS trigger AS begin  raise notice 'y_trigger: a = %', new.a;
  return new;
end;
 LANGUAGE plpgsql;
CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW    EXECUTE PROCEDURE y_trigger();
WITH t AS (    INSERT INTO y    VALUES        (21),        (22),        (23)    RETURNING *)SELECT * FROM t;
SELECT * FROM y;
DROP TRIGGER y_trig ON y;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW    EXECUTE PROCEDURE y_trigger();
WITH t AS (    INSERT INTO y    VALUES        (31),        (32),        (33)    RETURNING *)SELECT * FROM t LIMIT 1;
SELECT * FROM y;
DROP TRIGGER y_trig ON y;
CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS begin  raise notice 'y_trigger';
  return null;
end;
 LANGUAGE plpgsql;
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT    EXECUTE PROCEDURE y_trigger();
WITH t AS (    INSERT INTO y    VALUES        (41),        (42),        (43)    RETURNING *)SELECT * FROM t;
SELECT * FROM y;
DROP TRIGGER y_trig ON y;
DROP FUNCTION y_trigger();
CREATE TEMP TABLE parent ( id int, val text );
CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
INSERT INTO parent VALUES ( 1, 'p1' );
INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )UPDATE parent SET id = id + totalid FROM rcte;
SELECT * FROM parent;
WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )UPDATE parent SET id = id + newid FROM wcte;
SELECT * FROM parent;
WITH rcte AS ( SELECT max(id) AS maxid FROM parent )DELETE FROM parent USING rcte WHERE id = maxid;
SELECT * FROM parent;
WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )DELETE FROM parent USING wcte WHERE id = newid;
SELECT * FROM parent;
EXPLAIN (VERBOSE, COSTS OFF)WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )DELETE FROM a USING wcte WHERE aa = q2;
WITH RECURSIVE t AS (	INSERT INTO y		SELECT * FROM t)VALUES(FALSE);
WITH t AS (	INSERT INTO y VALUES(0))SELECT * FROM t;
SELECT * FROM (	WITH t AS (UPDATE y SET a=a+1 RETURNING *)	SELECT * FROM t) ss;
CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
WITH t AS (	INSERT INTO y VALUES(0))VALUES(FALSE);
DROP RULE y_rule ON y;
with ordinality as (select 1 as x) select * from ordinality;
WITH test AS (SELECT 42) INSERT INTO test VALUES (1);
create temp table test (i int);
with test as (select 42) insert into test select * from test;
select * from test;
drop table test;
